﻿using Common.Contract.Authentication.Record;
using Common.Contract.SystemConfig.Record;
using Customer.Core.Data;
using Customer.Model.Interface;
using Customer.Model.Model;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;

namespace Customer.Provider
{
    public class CustomerAlertProvider : GenericProvider, ICustomerAlertProvider
    {
        #region Alert badbank
        public List<AlertBadbankTopdown> RetrieveCustomerAlertBadBankTopdown(out int totalRecord, UserRecord dao, string lang, FilterRecord textSearch = null, int pagesize = 0, int pageindex = 0, bool isMax = false)
        {
            var data = repository.DbContext as CustomerEntities;
            var query = from x in data.AlertBadBanks
                        where x.BranchId != null
                        select x;
            var querySearch = query.Join(data.Branchs, x => x.BranchId, y => y.BranchId, (x, y) => new { cust = x, branch = y });
            DateTime ToDateCompare = DateTime.Now.AddMonths(1);
            DateTime FromDateCompare = DateTime.Now;
            if (!string.IsNullOrEmpty(textSearch.Region) && textSearch.Region.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.Region.ToUpper().Contains(x.branch.Region.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Branch) && textSearch.Branch.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.Branch.ToUpper().Contains(x.cust.BranchId.ToUpper()));
            }
            if (!string.IsNullOrEmpty(dao.Team) && dao.DaoSubOrdinatorList != null && dao.DaoSubOrdinatorList.Count > 0)
            {
                querySearch = querySearch.Where(x => dao.DaoSubOrdinatorList.Contains(x.cust.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Dao) && textSearch.Dao.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.Dao.Contains(x.cust.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.DaoName) && textSearch.DaoName.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.DaoName.Contains(x.cust.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Cif))
            {
                querySearch = querySearch.Where(x => x.cust.Cif.Contains(textSearch.Cif));
            }
            if (!string.IsNullOrEmpty(textSearch.CustName))
            {
                querySearch = querySearch.Where(x => x.cust.CusName.Contains(textSearch.CustName));
            }
            if (!string.IsNullOrEmpty(textSearch.Industry))
            {
                querySearch = lang.Equals("vi") ? querySearch.Where(x => x.cust.IndustryName.Contains(textSearch.Industry)) : querySearch.Where(x => x.cust.IndustryNameEn.Contains(textSearch.Industry));
            }
            if (!string.IsNullOrEmpty(textSearch.Color3))
            {
                switch (textSearch.Color3)
                {
                    case "0":
                        querySearch = querySearch.Where(x => x.cust.Month.Equals("Red"));
                        break;
                    case "1":
                        querySearch = querySearch.Where(x => x.cust.Month.Equals("Yellow"));
                        break;
                    case "2":
                        querySearch = querySearch.Where(x => x.cust.Month.Equals("Green"));
                        break;
                    default:
                        break;
                }
            }
            if (!string.IsNullOrEmpty(textSearch.Color2))
            {
                switch (textSearch.Color2)
                {
                    case "0":
                        querySearch = querySearch.Where(x => x.cust.Month1.Equals("Red"));
                        break;
                    case "1":
                        querySearch = querySearch.Where(x => x.cust.Month1.Equals("Yellow"));
                        break;
                    case "2":
                        querySearch = querySearch.Where(x => x.cust.Month1.Equals("Green"));
                        break;
                    default:
                        break;
                }
            }
            if (!string.IsNullOrEmpty(textSearch.Color1))
            {
                switch (textSearch.Color1)
                {
                    case "0":
                        querySearch = querySearch.Where(x => x.cust.Month2.Equals("Red"));
                        break;
                    case "1":
                        querySearch = querySearch.Where(x => x.cust.Month2.Equals("Yellow"));
                        break;
                    case "2":
                        querySearch = querySearch.Where(x => x.cust.Month2.Equals("Green"));
                        break;
                    default:
                        break;
                }
            }
            query = querySearch.OrderBy(x => new { x.branch.Region, x.branch.BranchId, x.cust.Dao }).Select(x => x.cust);
            totalRecord = query.Count();
            if (pagesize > 0 && pageindex >= 0)
            {
                query = query.Skip(pageindex * pagesize).Take(pagesize);
            }
            return query.ToList();
        }
        public List<AlertBadbankBottomup> RetrieveCustomerAlertBadBankBottomup(out int totalRecord, UserRecord dao, string lang, FilterRecord textSearch = null, int pagesize = 0, int pageindex = 0, bool isMax = false)
        {

            var data = repository.DbContext as CustomerEntities;
            var query = from x in data.AlertBadbankBottomups
                        where x.BranchId != null
                        select x;

            if (isMax == true)
            {
                string maxMonth = query.Max(x => x.Month);
                query = query.Where(x => x.Month.Equals(maxMonth));
            }
            var querySearch = query.Join(data.Branchs, x => x.BranchId, y => y.BranchId, (x, y) => new { cust = x, branch = y });
            DateTime ToDateCompare = DateTime.Now.AddMonths(1);
            DateTime FromDateCompare = DateTime.Now;
            if (!string.IsNullOrEmpty(textSearch.Region) && textSearch.Region.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.Region.ToUpper().Contains(x.branch.Region.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Branch) && textSearch.Branch.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.Branch.ToUpper().Contains(x.cust.BranchId.ToUpper()));
            }
            if (!string.IsNullOrEmpty(dao.Team) && dao.DaoSubOrdinatorList != null && dao.DaoSubOrdinatorList.Count > 0)
            {
                querySearch = querySearch.Where(x => dao.DaoSubOrdinatorList.Contains(x.cust.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Dao) && textSearch.Dao.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.Dao.Contains(x.cust.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.DaoName) && textSearch.DaoName.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.DaoName.Contains(x.cust.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Cif))
            {
                querySearch = querySearch.Where(x => x.cust.Cif.Contains(textSearch.Cif));
            }
            if (!string.IsNullOrEmpty(textSearch.CustName))
            {
                querySearch = querySearch.Where(x => x.cust.CusName.Contains(textSearch.CustName));
            }
            if (!string.IsNullOrEmpty(textSearch.Industry))
            {
                querySearch = lang.Equals("vi") ? querySearch.Where(x => x.cust.IndustryName.Contains(textSearch.Industry)) : querySearch.Where(x => x.cust.IndustryNameEn.Contains(textSearch.Industry));
            }
            if (!string.IsNullOrEmpty(textSearch.CusStatus))
            {
                switch (textSearch.CusStatus)
                {
                    case "0":
                        querySearch = querySearch.Where(x => x.cust.Color.Equals("Red"));
                        break;
                    case "1":
                        querySearch = querySearch.Where(x => x.cust.Color.Equals("Yellow"));
                        break;
                    case "2":
                        querySearch = querySearch.Where(x => x.cust.Color.Equals("Green"));
                        break;
                    default:
                        break;
                }

            }
            query = querySearch.OrderBy(x => new { x.branch.Region, x.branch.BranchId, x.cust.Dao }).Select(x => x.cust);
            totalRecord = query.Count();
            if (pagesize > 0 && pageindex >= 0)
            {
                query = query.Skip(pageindex * pagesize).Take(pagesize);
            }
            return query.ToList();
        }
        public List<AlertBadbankWatchlist> RetrieveCustomerAlertBadBankWatchlist(out int totalRecord, UserRecord dao, string lang, FilterRecord textSearch = null, int pagesize = 0, int pageindex = 0, bool isMax = false)
        {
            var data = repository.DbContext as CustomerEntities;
            var query = from x in data.AlertBadbankWatchlists
                        where x.BranchId != null
                        select x;

            if (isMax == true)
            {
                string maxMonth = query.Max(x => x.Month);
                query = query.Where(x => x.Month.Equals(maxMonth));
            }
            var querySearch = query.Join(data.Branchs, x => x.BranchId, y => y.BranchId, (x, y) => new { cust = x, branch = y });
            DateTime ToDateCompare = DateTime.Now.AddMonths(1);
            DateTime FromDateCompare = DateTime.Now;
            if (!string.IsNullOrEmpty(textSearch.Region) && textSearch.Region.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.Region.ToUpper().Contains(x.branch.Region.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Branch) && textSearch.Branch.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.Branch.ToUpper().Contains(x.cust.BranchId.ToUpper()));
            }
            if (!string.IsNullOrEmpty(dao.Team) && dao.DaoSubOrdinatorList != null && dao.DaoSubOrdinatorList.Count > 0)
            {
                querySearch = querySearch.Where(x => dao.DaoSubOrdinatorList.Contains(x.cust.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Dao) && textSearch.Dao.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.Dao.Contains(x.cust.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.DaoName) && textSearch.DaoName.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.DaoName.Contains(x.cust.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Cif))
            {
                querySearch = querySearch.Where(x => x.cust.Cif.Contains(textSearch.Cif));
            }
            if (!string.IsNullOrEmpty(textSearch.CustName))
            {
                querySearch = querySearch.Where(x => x.cust.CusName.Contains(textSearch.CustName));
            }
            if (!string.IsNullOrEmpty(textSearch.Industry))
            {
                querySearch = lang.Equals("vi") ? querySearch.Where(x => x.cust.IndustryName.Contains(textSearch.Industry)) : querySearch.Where(x => x.cust.IndustryNameEn.Contains(textSearch.Industry));
            }
            query = querySearch.OrderBy(x => new { x.branch.Region, x.branch.BranchId, x.cust.Dao }).Select(x => x.cust);
            totalRecord = query.Count();
            if (pagesize > 0 && pageindex >= 0)
            {
                query = query.Skip(pageindex * pagesize).Take(pagesize);
            }
            return query.ToList();
        }
        public List<BadBank> RetrieveCustomerBadBank(out int totalRecord, UserRecord dao, string lang, FilterRecord textSearch = null, int pagesize = 0, int pageindex = 0)
        {
            var data = repository.DbContext as CustomerEntities;
            var query = from x in data.BadBanks
                        where x.BranchId != null
                        select x;
            var querySearch = query.Join(data.Branchs, x => x.BranchId, y => y.BranchId, (x, y) => new { cust = x, branch = y });
            DateTime ToDateCompare = DateTime.Now.AddMonths(1);
            DateTime FromDateCompare = DateTime.Now;
            if (!string.IsNullOrEmpty(textSearch.Region) && textSearch.Region.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.Region.ToUpper().Contains(x.branch.Region.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Branch) && textSearch.Branch.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.Branch.ToUpper().Contains(x.cust.BranchId.ToUpper()));
            }
            if (!string.IsNullOrEmpty(dao.Team) && dao.DaoSubOrdinatorList != null && dao.DaoSubOrdinatorList.Count > 0)
            {
                querySearch = querySearch.Where(x => dao.DaoSubOrdinatorList.Contains(x.cust.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Dao) && textSearch.Dao.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.Dao.Contains(x.cust.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.DaoName) && textSearch.DaoName.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.DaoName.Contains(x.cust.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Cif))
            {
                querySearch = querySearch.Where(x => x.cust.Cif.Contains(textSearch.Cif));
            }
            if (!string.IsNullOrEmpty(textSearch.CustName))
            {
                querySearch = querySearch.Where(x => x.cust.Cusname.Contains(textSearch.CustName));
            }
            if (!string.IsNullOrEmpty(textSearch.Industry))
            {
                querySearch = lang.Equals("vi") ? querySearch.Where(x => x.cust.VpbIndustryName.Contains(textSearch.Industry)) : querySearch.Where(x => x.cust.VpbIndustry.Contains(textSearch.Industry));
            }
            if (!string.IsNullOrEmpty(textSearch.MovingInDate))
            {
                List<string> listFilter = textSearch.MovingInDate.Split('-').ToList();
                FromDateCompare = DateTime.ParseExact(listFilter.FirstOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                ToDateCompare = DateTime.ParseExact(listFilter.LastOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                querySearch = querySearch.Where(x => x.cust.MovingInDate >= FromDateCompare && x.cust.MovingInDate <= ToDateCompare);
            }
            query = querySearch.OrderBy(x => new { x.branch.Region, x.branch.BranchId, x.cust.Dao }).Select(x => x.cust);
            totalRecord = query.Count();
            if (pagesize > 0 && pageindex >= 0)
            {
                query = query.Skip(pageindex * pagesize).Take(pagesize);
            }
            return query.ToList();
        }

        #endregion
        #region Alert Maturity
        public List<TblAlertDepositMatdt> RetrieveTblAlertDepositMatdt(out int totalRecord, UserRecord dao, string lang, FilterRecord textSearch = null, int pagesize = 0, int pageindex = 0)
        {
            var data = repository.DbContext as CustomerEntities;
            DateTime ToDateCompare = DateTime.Now.AddMonths(1);
            DateTime FromDateCompare = DateTime.Now;
            var query = from x in data.TblAlertDepositMatdts
                        where x.Matdt >= FromDateCompare && x.Matdt <= ToDateCompare
                        select x;
            if (!string.IsNullOrEmpty(textSearch.Region) && textSearch.Region.Trim() != "null")
            {
                query = query.Where(x => textSearch.Region.ToUpper().Contains(x.Region.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Branch) && textSearch.Branch.Trim() != "null")
            {
                query = query.Where(x => textSearch.Branch.ToUpper().Contains(x.BranchCode.ToUpper()));
            }
            if (!string.IsNullOrEmpty(dao.Team) && dao.DaoSubOrdinatorList != null && dao.DaoSubOrdinatorList.Count > 0)
            {
                query = query.Where(x => dao.DaoSubOrdinatorList.Contains(x.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Dao) && textSearch.Dao.Trim() != "null")
            {
                query = query.Where(x => textSearch.Dao.Contains(x.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.DaoName) && textSearch.DaoName.Trim() != "null")
            {
                query = query.Where(x => textSearch.DaoName.Contains(x.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Cif))
            {
                query = query.Where(x => x.Cif.Contains(textSearch.Cif));
            }
            if (!string.IsNullOrEmpty(textSearch.CustName))
            {
                query = query.Where(x => x.CustomerName.Contains(textSearch.CustName));
            }
            if (!string.IsNullOrEmpty(textSearch.Matdt))
            {
                List<string> listFilter = textSearch.Matdt.Split('-').ToList();
                FromDateCompare = DateTime.ParseExact(listFilter.FirstOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                ToDateCompare = DateTime.ParseExact(listFilter.LastOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                query = query.Where(x => x.Matdt >= FromDateCompare && x.Matdt <= ToDateCompare);
            }
            List<string> listBalance = new List<string>();
            double fromValue = 0, toValue = 0;
            if (!string.IsNullOrEmpty(textSearch.Balance))
            {
                listBalance = textSearch.Balance.Split(';').ToList();
                fromValue = !string.IsNullOrEmpty(listBalance.FirstOrDefault()) ? double.Parse(listBalance.FirstOrDefault()) : 0;
                toValue = !string.IsNullOrEmpty(listBalance.LastOrDefault()) ? double.Parse(listBalance.LastOrDefault()) : (double)1e18;
                query = query.Where(x => x.BalQd > fromValue && x.BalQd < toValue);
            }
            if (!string.IsNullOrEmpty(textSearch.Term))
            {
                query = query.Where(x => x.Term.Contains(textSearch.Term));
            }
            query = query.OrderBy(x => new { x.Region, x.BranchCode, x.Dao });
            totalRecord = query.Count();
            if (pagesize > 0 && pageindex >= 0)
            {
                query = query.Skip(pageindex * pagesize).Take(pagesize);
            }
            return query.ToList();
        }
        public List<LoanMatdt> RetrieveCustomerLoanMatdt(out int totalRecord, UserRecord dao, string lang, FilterRecord textSearch = null, int pagesize = 0, int pageindex = 0)
        {
            var data = repository.DbContext as CustomerEntities;
            DateTime ToDateCompare = DateTime.Now.AddMonths(1);
            DateTime FromDateCompare = DateTime.Now;
            var query = from x in data.LoanMatdts
                        where x.BranchId != null && x.Matdt >= FromDateCompare && x.Matdt <= ToDateCompare
                        select x;
            var querySearch = query.Join(data.Branchs, x => x.BranchId, y => y.BranchId, (x, y) => new { cust = x, branch = y });
            if (!string.IsNullOrEmpty(textSearch.Region) && textSearch.Region.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.Region.ToUpper().Contains(x.branch.Region.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Branch) && textSearch.Branch.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.Branch.ToUpper().Contains(x.cust.BranchId.ToUpper()));
            }
            if (!string.IsNullOrEmpty(dao.Team) && dao.DaoSubOrdinatorList != null && dao.DaoSubOrdinatorList.Count > 0)
            {
                querySearch = querySearch.Where(x => dao.DaoSubOrdinatorList.Contains(x.cust.DaoCif));
            }
            if (!string.IsNullOrEmpty(textSearch.Dao) && textSearch.Dao.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.Dao.Contains(x.cust.DaoCif));
            }
            if (!string.IsNullOrEmpty(textSearch.DaoName) && textSearch.DaoName.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.DaoName.Contains(x.cust.DaoCif));
            }
            if (!string.IsNullOrEmpty(textSearch.Cif))
            {
                querySearch = querySearch.Where(x => x.cust.Cif.Contains(textSearch.Cif));
            }
            if (!string.IsNullOrEmpty(textSearch.CustName))
            {
                querySearch = querySearch.Where(x => x.cust.CustomerName.Contains(textSearch.CustName));
            }
            if (!string.IsNullOrEmpty(textSearch.Matdt))
            {
                List<string> listFilter = textSearch.Matdt.Split('-').ToList();
                FromDateCompare = DateTime.ParseExact(listFilter.FirstOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                ToDateCompare = DateTime.ParseExact(listFilter.LastOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                querySearch = querySearch.Where(x => x.cust.Matdt >= FromDateCompare && x.cust.Matdt <= ToDateCompare);
            }
            List<string> listBalance = new List<string>();
            decimal fromValue = 0, toValue = 0;
            if (!string.IsNullOrEmpty(textSearch.Balance))
            {
                listBalance = textSearch.Balance.Split(';').ToList();
                fromValue = !string.IsNullOrEmpty(listBalance.FirstOrDefault()) ? decimal.Parse(listBalance.FirstOrDefault()) : 0;
                toValue = !string.IsNullOrEmpty(listBalance.LastOrDefault()) ? decimal.Parse(listBalance.LastOrDefault()) : (decimal)1e18;
                querySearch = querySearch.Where(x => x.cust.BalQd > fromValue && x.cust.BalQd < toValue);
            }
            query = querySearch.OrderBy(x => new { x.branch.Region, x.branch.BranchId, x.cust.DaoCif }).Select(x => x.cust);
            totalRecord = query.Count();
            if (pagesize > 0 && pageindex >= 0)
            {
                query = query.Skip(pageindex * pagesize).Take(pagesize);
            }
            return query.ToList();
        }
        public List<AlertLimit> RetrieveCustomerAlertLimit(out int totalRecord, UserRecord dao, string lang, FilterRecord textSearch = null, int pagesize = 0, int pageindex = 0)
        {
            var data = repository.DbContext as CustomerEntities;

            DateTime ToDateCompare = DateTime.Now.AddMonths(1);
            DateTime FromDateCompare = DateTime.Now;

            var query = from x in data.AlertLimits
                        where x.ExpiryDate >= FromDateCompare && x.ExpiryDate <= ToDateCompare
                        select x;

            if (!string.IsNullOrEmpty(textSearch.Region) && textSearch.Region.Trim() != "null")
            {
                query = query.Where(x => textSearch.Region.ToUpper().Contains(x.Region.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Branch) && textSearch.Branch.Trim() != "null")
            {
                query = query.Where(x => textSearch.Branch.ToUpper().Contains(x.BranchId.ToUpper()));
            }
            if (!string.IsNullOrEmpty(dao.Team) && dao.DaoSubOrdinatorList != null && dao.DaoSubOrdinatorList.Count > 0)
            {
                query = query.Where(x => dao.DaoSubOrdinatorList.Contains(x.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Dao) && textSearch.Dao.Trim() != "null")
            {
                query = query.Where(x => textSearch.Dao.Contains(x.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.DaoName) && textSearch.DaoName.Trim() != "null")
            {
                query = query.Where(x => textSearch.DaoName.Contains(x.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Cif))
            {
                query = query.Where(x => x.Cif.Contains(textSearch.Cif));
            }
            if (!string.IsNullOrEmpty(textSearch.CustName))
            {
                query = query.Where(x => x.CusName.Contains(textSearch.CustName));
            }
            if (!string.IsNullOrEmpty(textSearch.Matdt))
            {
                List<string> listFilter = textSearch.Matdt.Split('-').ToList();
                FromDateCompare = DateTime.ParseExact(listFilter.FirstOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                ToDateCompare = DateTime.ParseExact(listFilter.LastOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                query = query.Where(x => x.ExpiryDate >= FromDateCompare && x.ExpiryDate <= ToDateCompare);
            }
            if (!string.IsNullOrEmpty(textSearch.Acctno))
            {
                query = query.Where(x => x.Id.Contains(textSearch.Acctno));
            }
            totalRecord = query.Count();
            query = query.OrderBy(x => new { x.Region, x.BranchId, x.Dao });
            if (pagesize > 0 && pageindex >= 0)
            {
                query = query.Skip(pageindex * pagesize).Take(pagesize);
            }
            return query.ToList();
        }
        public List<AlertBL> RetrieveCustomerAlertBL(out int totalRecord, UserRecord dao, string lang, FilterRecord textSearch = null, int pagesize = 0, int pageindex = 0)
        {
            var data = repository.DbContext as CustomerEntities;
            DateTime ToDateCompare = DateTime.Now.AddMonths(1);
            DateTime FromDateCompare = DateTime.Now;

            var query = from x in data.AlertBLs
                        where x.MaturityDate >= FromDateCompare && x.MaturityDate <= ToDateCompare
                        select x;

            if (!string.IsNullOrEmpty(textSearch.Region) && textSearch.Region.Trim() != "null")
            {
                query = query.Where(x => textSearch.Region.ToUpper().Contains(x.Region.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Branch) && textSearch.Branch.Trim() != "null")
            {
                query = query.Where(x => textSearch.Branch.ToUpper().Contains(x.BranchId.ToUpper()));
            }
            if (!string.IsNullOrEmpty(dao.Team) && dao.DaoSubOrdinatorList != null && dao.DaoSubOrdinatorList.Count > 0)
            {
                query = query.Where(x => dao.DaoSubOrdinatorList.Contains(x.DaoCif));
            }
            if (!string.IsNullOrEmpty(textSearch.Dao) && textSearch.Dao.Trim() != "null")
            {
                query = query.Where(x => textSearch.Dao.Contains(x.DaoCif));
            }
            if (!string.IsNullOrEmpty(textSearch.DaoName) && textSearch.DaoName.Trim() != "null")
            {
                query = query.Where(x => textSearch.DaoName.Contains(x.DaoCif));
            }
            if (!string.IsNullOrEmpty(textSearch.Cif))
            {
                query = query.Where(x => x.Cif.Contains(textSearch.Cif));
            }
            if (!string.IsNullOrEmpty(textSearch.CustName))
            {
                query = query.Where(x => x.CusName.Contains(textSearch.CustName));
            }
            if (!string.IsNullOrEmpty(textSearch.Matdt))
            {
                List<string> listFilter = textSearch.Matdt.Split('-').ToList();
                FromDateCompare = DateTime.ParseExact(listFilter.FirstOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                ToDateCompare = DateTime.ParseExact(listFilter.LastOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                query = query.Where(x => x.MaturityDate >= FromDateCompare && x.MaturityDate <= ToDateCompare);
            }
            if (!string.IsNullOrEmpty(textSearch.Acctno))
            {
                query = query.Where(x => x.MdId.Contains(textSearch.Acctno));
            }
            totalRecord = query.Count();
            query = query.OrderBy(x => new { x.Region, x.BranchId, x.DaoCif });
            if (pagesize > 0 && pageindex >= 0)
            {
                query = query.Skip(pageindex * pagesize).Take(pagesize);
            }
            return query.ToList();
        }
        public List<AlertLC> RetrieveCustomerAlertLC(out int totalRecord, UserRecord dao, string lang, FilterRecord textSearch = null, int pagesize = 0, int pageindex = 0)
        {
            var data = repository.DbContext as CustomerEntities;
            DateTime ToDateCompare = DateTime.Now.AddMonths(1);
            DateTime FromDateCompare = DateTime.Now;
            var query = from x in data.AlertLCs
                        where x.ExpiryDate >= FromDateCompare && x.ExpiryDate <= ToDateCompare
                        select x;

            if (!string.IsNullOrEmpty(textSearch.Region) && textSearch.Region.Trim() != "null")
            {
                query = query.Where(x => textSearch.Region.ToUpper().Contains(x.Region.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Branch) && textSearch.Branch.Trim() != "null")
            {
                query = query.Where(x => textSearch.Branch.ToUpper().Contains(x.BranchId.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Dao))
            {
                query = query.Where(x => x.DaoCif.Contains(textSearch.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.DaoName))
            {
                query = query.Where(x => x.DaoName.Contains(textSearch.DaoName));
            }
            if (!string.IsNullOrEmpty(textSearch.Cif))
            {
                query = query.Where(x => x.Cif.Contains(textSearch.Cif));
            }
            if (!string.IsNullOrEmpty(textSearch.CustName))
            {
                query = query.Where(x => x.CusName.Contains(textSearch.CustName));
            }
            if (!string.IsNullOrEmpty(textSearch.Matdt))
            {
                List<string> listFilter = textSearch.Matdt.Split('-').ToList();
                FromDateCompare = DateTime.ParseExact(listFilter.FirstOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                ToDateCompare = DateTime.ParseExact(listFilter.LastOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                query = query.Where(x => x.ExpiryDate >= FromDateCompare && x.ExpiryDate <= ToDateCompare);
            }
            if (!string.IsNullOrEmpty(textSearch.Acctno))
            {
                query = query.Where(x => x.TransId.Contains(textSearch.Acctno));
            }
            totalRecord = query.Count();
            query = query.OrderBy(x => new { x.Region, x.BranchId, x.DaoCif });
            if (pagesize > 0 && pageindex >= 0)
            {
                query = query.Skip(pageindex * pagesize).Take(pagesize);
            }
            return query.ToList();
        }
        #endregion
        #region Other Alert
        public List<AlertGeneral> RetrieveAlertGeneralByDao(out int totalRecord, UserRecord dao, string lang, FilterRecord textSearch = null, int pagesize = 0, int pageindex = 0)
        {
            var data = repository.DbContext as CustomerEntities;
            DateTime ToDateCompare = DateTime.Now.AddMonths(1);
            DateTime FromDateCompare = DateTime.Now;
            var query = from x in data.AlertGenerals
                        where x.BranchId != null
                        select x;
            var querySearch = query.Join(data.Branchs, x => x.BranchId, y => y.BranchId, (x, y) => new { cust = x, branch = y });
            if (!string.IsNullOrEmpty(textSearch.Region) && textSearch.Region.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.Region.ToUpper().Contains(x.branch.Region.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Branch) && textSearch.Branch.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.Branch.ToUpper().Contains(x.cust.BranchId.ToUpper()));
            }
            if (!string.IsNullOrEmpty(dao.Team) && dao.DaoSubOrdinatorList != null && dao.DaoSubOrdinatorList.Count > 0)
            {
                querySearch = querySearch.Where(x => dao.DaoSubOrdinatorList.Contains(x.cust.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Dao) && textSearch.Dao.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.Dao.Contains(x.cust.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.DaoName) && textSearch.DaoName.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.DaoName.Contains(x.cust.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Cif))
            {
                querySearch = querySearch.Where(x => x.cust.Cif.Contains(textSearch.Cif));
            }
            if (!string.IsNullOrEmpty(textSearch.CustName))
            {
                querySearch = querySearch.Where(x => x.cust.CustomerName.Contains(textSearch.CustName));
            }
            query = querySearch.OrderBy(x => new { x.branch.Region, x.branch.BranchId, x.cust.Dao }).Select(x => x.cust);
            totalRecord = query.Count();
            if (pagesize > 0 && pageindex >= 0)
            {
                query = query.Skip(pageindex * pagesize).Take(pagesize);
            }
            return query.ToList();
        }
        public List<AlertCasa> RetrieveCustomerAlertCasa(out int totalRecord, UserRecord dao, string lang, FilterRecord textSearch = null, int pagesize = 0, int pageindex = 0, bool isMax = false)
        {
            var data = repository.DbContext as CustomerEntities;
            var query = from x in data.AlertCasas
                        where x.Delta4 <= -50000000 && x.Delta3 <= -50000000 && x.Delta2 <= -50000000
                        select x;
            if (isMax == true)
            {
                string maxMonth = query.Max(x => x.Month);
                query = query.Where(x => x.Month.Equals(maxMonth));
            }
            var querySearch = query.Join(data.Branchs, x => x.BranchId, y => y.BranchId, (x, y) => new { cust = x, branch = y });
            DateTime ToDateCompare = DateTime.Now.AddMonths(1);
            DateTime FromDateCompare = DateTime.Now;
            if (!string.IsNullOrEmpty(textSearch.Region) && textSearch.Region.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.Region.ToUpper().Contains(x.branch.Region.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Branch) && textSearch.Branch.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.Branch.ToUpper().Contains(x.cust.BranchId.ToUpper()));
            }
            if (!string.IsNullOrEmpty(dao.Team) && dao.DaoSubOrdinatorList != null && dao.DaoSubOrdinatorList.Count > 0)
            {
                querySearch = querySearch.Where(x => dao.DaoSubOrdinatorList.Contains(x.cust.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Dao) && textSearch.Dao.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.Dao.Contains(x.cust.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.DaoName) && textSearch.DaoName.Trim() != "null")
            {
                querySearch = querySearch.Where(x => textSearch.DaoName.Contains(x.cust.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Cif))
            {
                querySearch = querySearch.Where(x => x.cust.Cif.Contains(textSearch.Cif));
            }
            if (!string.IsNullOrEmpty(textSearch.CustName))
            {
                querySearch = querySearch.Where(x => x.cust.CusName.Contains(textSearch.CustName));
            }
            List<string> listBalance = new List<string>();
            double fromValue = 0, toValue = 0;
            if (!string.IsNullOrEmpty(textSearch.Balance4))
            {
                listBalance = textSearch.Balance4.Split('-').ToList();
                fromValue = !string.IsNullOrEmpty(listBalance.FirstOrDefault()) ? double.Parse(listBalance.FirstOrDefault()) : 0;
                toValue = !string.IsNullOrEmpty(listBalance.LastOrDefault()) ? double.Parse(listBalance.LastOrDefault()) : (double)1e18;
                querySearch = querySearch.Where(x => x.cust.AvgbalQdN1 > fromValue && x.cust.AvgbalQdN1 < toValue);
            }
            if (!string.IsNullOrEmpty(textSearch.Balance3))
            {
                listBalance = textSearch.Balance3.Split('-').ToList();
                fromValue = !string.IsNullOrEmpty(listBalance.FirstOrDefault()) ? double.Parse(listBalance.FirstOrDefault()) : 0;
                toValue = !string.IsNullOrEmpty(listBalance.LastOrDefault()) ? double.Parse(listBalance.LastOrDefault()) : (double)1e18;
                querySearch = querySearch.Where(x => x.cust.AvgbalQdN2 > fromValue && x.cust.AvgbalQdN2 < toValue);
            }
            if (!string.IsNullOrEmpty(textSearch.Balance2))
            {
                listBalance = textSearch.Balance2.Split('-').ToList();
                fromValue = !string.IsNullOrEmpty(listBalance.FirstOrDefault()) ? double.Parse(listBalance.FirstOrDefault()) : 0;
                toValue = !string.IsNullOrEmpty(listBalance.LastOrDefault()) ? double.Parse(listBalance.LastOrDefault()) : (double)1e18;
                querySearch = querySearch.Where(x => x.cust.AvgbalQdN3 > fromValue && x.cust.AvgbalQdN3 < toValue);
            }
            if (!string.IsNullOrEmpty(textSearch.Balance1))
            {
                listBalance = textSearch.Balance1.Split('-').ToList();
                fromValue = !string.IsNullOrEmpty(listBalance.FirstOrDefault()) ? double.Parse(listBalance.FirstOrDefault()) : 0;
                toValue = !string.IsNullOrEmpty(listBalance.LastOrDefault()) ? double.Parse(listBalance.LastOrDefault()) : (double)1e18;
                querySearch = querySearch.Where(x => x.cust.AvgbalQdN4 > fromValue && x.cust.AvgbalQdN4 < toValue);
            }
            query = querySearch.OrderBy(x => new { x.branch.Region, x.branch.BranchId, x.cust.Dao }).Select(x => x.cust);
            totalRecord = query.Count();
            if (pagesize > 0 && pageindex >= 0)
            {
                query = query.Skip(pageindex * pagesize).Take(pagesize);
            }
            return query.ToList();
        }
        public List<AlertInactive> RetrieveCustomerAlertInactive(out int totalRecord, UserRecord dao, string lang, FilterRecord textSearch = null, int pagesize = 0, int pageindex = 0)
        {
            var data = repository.DbContext as CustomerEntities;
            var query = from x in data.AlertInactives
                        //where x.Dao.ToUpper() == dao.ToUpper()
                        select x;
            DateTime ToDateCompare = DateTime.Now.AddMonths(1);
            DateTime FromDateCompare = DateTime.Now;
            if (!string.IsNullOrEmpty(textSearch.Region) && textSearch.Region.Trim() != "null")
            {
                query = query.Where(x => textSearch.Region.ToUpper().Contains(x.Region.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Branch) && textSearch.Branch.Trim() != "null")
            {
                query = query.Where(x => textSearch.Branch.ToUpper().Contains(x.BranchId.ToUpper()));
            }
            if (!string.IsNullOrEmpty(dao.Team) && dao.DaoSubOrdinatorList != null && dao.DaoSubOrdinatorList.Count > 0)
            {
                query = query.Where(x => dao.DaoSubOrdinatorList.Contains(x.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Dao) && textSearch.Dao.Trim() != "null")
            {
                query = query.Where(x => textSearch.Dao.Contains(x.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.DaoName) && textSearch.DaoName.Trim() != "null")
            {
                query = query.Where(x => textSearch.DaoName.Contains(x.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Cif))
            {
                query = query.Where(x => x.Cif.Contains(textSearch.Cif));
            }
            if (!string.IsNullOrEmpty(textSearch.CustName))
            {
                query = query.Where(x => x.CusName.Contains(textSearch.CustName));
            }
            if (!string.IsNullOrEmpty(textSearch.Industry))
            {
                query = lang.Equals("vi") ? query.Where(x => x.IndustryName.Contains(textSearch.Industry)) : query.Where(x => x.IndustryNameEn.Contains(textSearch.Industry));
            }
            if (!string.IsNullOrEmpty(textSearch.CusOpenDate))
            {
                List<string> listFilter = textSearch.CusOpenDate.Split('-').ToList();
                FromDateCompare = DateTime.ParseExact(listFilter.FirstOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                ToDateCompare = DateTime.ParseExact(listFilter.LastOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                query = query.Where(x => x.CusOpenDate >= FromDateCompare && x.CusOpenDate <= ToDateCompare);
            }
            totalRecord = query.Count();
            query = query.OrderBy(x => new { x.Region, x.BranchId, x.Dao });
            if (pagesize > 0 && pageindex >= 0)
            {
                query = query.Skip(pageindex * pagesize).Take(pagesize);
            }
            return query.ToList();
        }
        #endregion
        #region Alert Overdue
        public List<AlertOverdue> RetrieveCustomerOverdue(out int totalRecord, UserRecord dao, string lang, FilterRecord textSearch = null, int pagesize = 0, int pageindex = 0)
        {
            var data = repository.DbContext as CustomerEntities;
            var query = from x in data.AlertOverdues
                        where x.BranchCodeSme != null
                        select x;
            if (!string.IsNullOrEmpty(textSearch.Region) && textSearch.Region.Trim() != "null")
            {
                query = query.Where(x => textSearch.Region.ToUpper().Contains(x.Region.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Branch) && textSearch.Branch.Trim() != "null")
            {
                query = query.Join(data.Branchs, x => x.BranchCodeSme, y => y.BranchCodeSme, (x, y) => new { cust = x, branch = y }).Where(x => textSearch.Branch.ToUpper().Contains(x.branch.BranchId.ToUpper())).Select(x => x.cust);
            }
            if (!string.IsNullOrEmpty(dao.Team) && dao.DaoSubOrdinatorList != null && dao.DaoSubOrdinatorList.Count > 0)
            {
                query = query.Where(x => dao.DaoSubOrdinatorList.Contains(x.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Dao) && textSearch.Dao.Trim() != "null")
            {
                query = query.Where(x => textSearch.Dao.Contains(x.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.DaoName) && textSearch.DaoName.Trim() != "null")
            {
                query = query.Where(x => textSearch.DaoName.Contains(x.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Cif))
            {
                query = query.Where(x => x.Cif.Contains(textSearch.Cif));
            }
            if (!string.IsNullOrEmpty(textSearch.CustName))
            {
                query = query.Where(x => x.CusName.Contains(textSearch.CustName));
            }
            if (!string.IsNullOrEmpty(textSearch.Acctno))
            {
                query = query.Where(x => x.ContractNo.Contains(textSearch.Acctno));
            }
            //if (!string.IsNullOrEmpty(textSearch.Acctno))
            //{
            //    query = query.Where(x => x.ContractNo.Contains(textSearch.Acctno));
            //}
            List<string> listOverdueDate = new List<string>();
            if (!string.IsNullOrEmpty(textSearch.NoOverdueDate))
            {
                listOverdueDate = textSearch.NoOverdueDate.Split('-').ToList();
                var fromValue = !string.IsNullOrEmpty(listOverdueDate.FirstOrDefault()) ? decimal.Parse(listOverdueDate.FirstOrDefault()) : 0;
                var toValue = !string.IsNullOrEmpty(listOverdueDate.LastOrDefault()) ? decimal.Parse(listOverdueDate.LastOrDefault()) : (decimal)1e18;
                query = query.Where(x => x.NoOvdDays > fromValue && x.NoOvdDays < toValue);
            }
            query = query.OrderBy(x => new { x.Region, x.BranchNameSme, x.Dao });
            totalRecord = query.Count();
            if (pagesize > 0 && pageindex >= 0)
            {
                query = query.Skip(pageindex * pagesize).Take(pagesize);
            }
            return query.ToList();
        }

        public List<AlertCardOverdue> RetrieveCustomerCardOverdue(out int totalRecord, UserRecord dao, string lang, FilterRecord textSearch = null, int pagesize = 0, int pageindex = 0)
        {
            var data = repository.DbContext as CustomerEntities;
            var query = from x in data.AlertCardOverdues
                        where x.BranchId != null
                        select x;
            if (!string.IsNullOrEmpty(textSearch.Region) && textSearch.Region.Trim() != "null")
            {
                query = query.Where(x => textSearch.Region.ToUpper().Contains(x.Region.ToUpper()));
            }
            if (!string.IsNullOrEmpty(textSearch.Branch) && textSearch.Branch.Trim() != "null")
            {
                query = query.Join(data.Branchs, x => x.BranchId, y => y.BranchId, (x, y) => new { cust = x, branch = y }).Where(x => textSearch.Branch.ToUpper().Contains(x.branch.BranchId.ToUpper())).Select(x => x.cust);
            }
            if (!string.IsNullOrEmpty(dao.Team) && dao.DaoSubOrdinatorList != null && dao.DaoSubOrdinatorList.Count > 0)
            {
                query = query.Where(x => dao.DaoSubOrdinatorList.Contains(x.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Dao) && textSearch.Dao.Trim() != "null")
            {
                query = query.Where(x => textSearch.Dao.Contains(x.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.DaoName) && textSearch.DaoName.Trim() != "null")
            {
                query = query.Where(x => textSearch.DaoName.Contains(x.Dao));
            }
            if (!string.IsNullOrEmpty(textSearch.Cif))
            {
                query = query.Where(x => x.ContractCif.Contains(textSearch.Cif));
            }
            if (!string.IsNullOrEmpty(textSearch.CustName))
            {
                query = query.Where(x => x.CusName.Contains(textSearch.CustName));
            }
            if (!string.IsNullOrEmpty(textSearch.Acctno))
            {
                query = query.Where(x => x.ContractNumber.Contains(textSearch.Acctno));
            }
            //if (!string.IsNullOrEmpty(textSearch.Acctno))
            //{
            //    query = query.Where(x => x.ContractNo.Contains(textSearch.Acctno));
            //}
            DateTime ToDateCompare = DateTime.Now.AddMonths(1);
            DateTime FromDateCompare = DateTime.Now;
            if (!string.IsNullOrEmpty(textSearch.DateOvd))
            {
                List<string> listFilter = textSearch.DateOvd.Split('-').ToList();
                FromDateCompare = DateTime.ParseExact(listFilter.FirstOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                ToDateCompare = DateTime.ParseExact(listFilter.LastOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                query = query.Where(x => x.OvdDate >= FromDateCompare && x.OvdDate <= ToDateCompare);
            }
            List<string> listOverdueDate = new List<string>();
            if (!string.IsNullOrEmpty(textSearch.NoOverdueDate))
            {
                listOverdueDate = textSearch.NoOverdueDate.Split(';').ToList();
                var fromValue = !string.IsNullOrEmpty(listOverdueDate.FirstOrDefault()) ? decimal.Parse(listOverdueDate.FirstOrDefault()) : 0;
                var toValue = !string.IsNullOrEmpty(listOverdueDate.LastOrDefault()) ? decimal.Parse(listOverdueDate.LastOrDefault()) : (decimal)1e18;
                query = query.Where(x => x.OvdDays > fromValue && x.OvdDays < toValue);
            }
            query = query.OrderBy(x => new { x.Region, x.BranchNameSme, x.Dao });
            totalRecord = query.Count();
            if (pagesize > 0 && pageindex >= 0)
            {
                query = query.Skip(pageindex * pagesize).Take(pagesize);
            }
            return query.ToList();
        }
        #endregion
        #region Celebration
        public List<CustomerRegion> RetrieveCustomerCelebrationByDao(out int totalRecord, UserRecord dao, FilterRecord textSearch = null, string lang = null, int pagesize = 0, int pageindex = 0)
        {
            var data = repository.DbContext as CustomerEntities;
            var query = from x in data.CustomerRegions
                        select x;

            IQueryable<CustomerBranch> querySearch = query.Join(data.Branchs, x => x.BranchId, y => y.BranchId, (x, y) => new CustomerBranch { Cust = x, Branch = y });
            querySearch = FilterCustomerRegionByCondition(querySearch, dao, textSearch, lang);

            DateTime ToDateCompare = DateTime.Now.AddMonths(1);
            DateTime FromDateCompare = DateTime.Now;
            if (!string.IsNullOrEmpty(textSearch.CustBirthDate))
            {
                List<string> listFilter = textSearch.CustBirthDate.Split('-').ToList();
                FromDateCompare = DateTime.ParseExact(listFilter.FirstOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                ToDateCompare = DateTime.ParseExact(listFilter.LastOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
            }
            querySearch = querySearch.Where(x => (x.Cust.BirthIncorpDate.Value.Month == FromDateCompare.Month && x.Cust.BirthIncorpDate.Value.Month != ToDateCompare.Month && x.Cust.BirthIncorpDate.Value.Day >= FromDateCompare.Day) || (x.Cust.BirthIncorpDate.Value.Month != FromDateCompare.Month && x.Cust.BirthIncorpDate.Value.Month == ToDateCompare.Month && x.Cust.BirthIncorpDate.Value.Day <= ToDateCompare.Day) || (FromDateCompare.Month == ToDateCompare.Month && x.Cust.BirthIncorpDate.Value.Month == FromDateCompare.Month && x.Cust.BirthIncorpDate.Value.Day >= FromDateCompare.Day && x.Cust.BirthIncorpDate.Value.Day <= ToDateCompare.Day));
            query = querySearch.OrderBy(x => new { x.Branch.Region, x.Branch.BranchId, x.Cust.Dao }).Select(x => x.Cust);
            totalRecord = query.Count();
            if (pagesize > 0 && pageindex >= 0)
            {
                query = query.Skip(pageindex * pagesize).Take(pagesize);
            }
            return query.ToList();
        }

        public List<CustomerRelation> RetrieveAllCustomerRelation(UserRecord dao, FilterRecord textSearch = null)
        {
            var data = repository.DbContext as CustomerEntities;
            var query = from x in data.CustomerRelations
                        where x.BirOfRelCust != null
                        select x;
            if (!string.IsNullOrEmpty(textSearch.RelationName))
            {
                query = query.Where(x => x.CusRelName.Contains(textSearch.RelationName));
            }
            if (!string.IsNullOrEmpty(textSearch.RelationPosition))
            {
                query = query.Where(x => x.CusRelPos.Contains(textSearch.RelationPosition));
            }
            DateTime ToDateCompare = DateTime.Now.AddMonths(1);
            DateTime FromDateCompare = DateTime.Now;
            if (!string.IsNullOrEmpty(textSearch.RelationBirthDate))
            {
                List<string> listFilter = textSearch.RelationBirthDate.Split('-').ToList();
                FromDateCompare = DateTime.ParseExact(listFilter.FirstOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                ToDateCompare = DateTime.ParseExact(listFilter.LastOrDefault().Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
            }
            query = query.Where(y => (y.BirOfRelCust.Value.Month != ToDateCompare.Month && y.BirOfRelCust.Value.Month == FromDateCompare.Month && y.BirOfRelCust.Value.Day >= FromDateCompare.Day) || (y.BirOfRelCust.Value.Month != FromDateCompare.Month && y.BirOfRelCust.Value.Month == ToDateCompare.Month && y.BirOfRelCust.Value.Day <= ToDateCompare.Day) || (FromDateCompare.Month == ToDateCompare.Month && y.BirOfRelCust.Value.Month == FromDateCompare.Month && y.BirOfRelCust.Value.Day >= FromDateCompare.Day && y.BirOfRelCust.Value.Day <= ToDateCompare.Day));
            return query.ToList();
        }

        public CustomerRelation RetrieveCustomerRelationByCif(string relCif)
        {
            var data = repository.DbContext as CustomerEntities;
            var query = from x in data.CustomerRelations
                        where x.Cif == relCif
                        select x;
            return query.FirstOrDefault();
        }
        #endregion
    }
}
